package org.alex.samples.util;


import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import java.util.List;

import org.alex.samples.model.UserVO;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtils {

	public static final Object OFFICE_EXCEL_2003_POSTFIX = ".xls";
	public static final Object OFFICE_EXCEL_2007_POSTFIX = ".xlsx";

	public static Workbook openWorkbook(InputStream is, String postfix) {
		Workbook hssfworkbook = null;
		try {
			if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
				hssfworkbook = new HSSFWorkbook(is);
			} else if (OFFICE_EXCEL_2007_POSTFIX.equals(postfix)) {
				hssfworkbook = new XSSFWorkbook(is);
			}

		} catch (Exception e) {
			if (e instanceof NotOLE2FileException) {
				return null;
			}
			e.printStackTrace();
		}
		return hssfworkbook;
	}

	public static List<UserVO> getUserListByExcel(Workbook wk) {
		final String GENDER_MALE_STRING = "男", GENDER_FEMALE_STRING = "女";
		final Integer GENDER_MALE_INTEGER = 1, GENDER_FEMALE_INTEGER = 0;
		List<UserVO> resultList = new ArrayList<UserVO>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		UserVO vo = null;
		for (int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber++) {
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			vo = new UserVO();
			for (int currentCellNumber = 0; currentCellNumber < maxCellNumber; currentCellNumber++) {
				Cell cell = row.getCell(currentCellNumber);
				switch (currentCellNumber) {
				case 0:
					// 获取学号列数据
					vo.setId(getCellValue(cell));
					break;

				case 1:
					// 获取姓名列数据
					vo.setName(getCellValue(cell));
					break;
				case 2:
					
					// 获取密码列数据
					String decodingPassword = getCellValue(cell);
					vo.setPassword(MD5Util.toMD5(decodingPassword));
					break;
				case 3:
					// 获取联系电话列数据
					vo.setTel(getCellValue(cell));
					break;
				case 4:
					// 获取电子邮箱列数据
					vo.setEmail(getCellValue(cell));
					break;
				case 5:
					// 获取地址列数据
					vo.setAddress(getCellValue(cell));
					break;
				case 6:
					// 获取出生日期列数据
					try {
						vo.setBirthday(new SimpleDateFormat("yyyy-MM-dd").parse(getCellValue(cell)));
					} catch (ParseException e) {
						e.printStackTrace();
					}
					break;

				case 7:
					// 获取性别列数据
					String gender = getCellValue(cell);
					// 判断如果为'男'存入1，‘女’则存入0
					if (GENDER_MALE_STRING.equals(gender)) {
						vo.setGender(GENDER_MALE_INTEGER);
					} else if (GENDER_FEMALE_STRING.equals(gender)) {
						vo.setGender(GENDER_FEMALE_INTEGER);
					}
					break;
				}
			}
			resultList.add(vo);
		}
		return resultList;

	}

	private static String getCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}

		if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
			DecimalFormat df = new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

}
